package com.erp.framework.util.excelupload;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.erp.framework.util.logger.Logger;

public class ExcelRowDao {

	public String getQuerySql() {
		String sql = "SELECT TRIM(UPPER(DB_COLUMN)) DB_COLUMN, TRIM(UPPER(EXCEL_LABEL)) EXCEL_LABEL, CELL_TYPE FROM T_EXCEL_MAPPING ";
		return sql;
	}

    public ExcelCellVo getResultSetValues(ResultSet rs) throws SQLException {
    	String dbColumn = rs.getString("DB_COLUMN");
    	String excelLabel = rs.getString("EXCEL_LABEL");
    	ExcelCellVo cell = new ExcelCellVo(dbColumn, excelLabel);
    	//more fields related to cell properties can be added 
    	cell.setCellType(rs.getString("CELL_TYPE"));
    	return cell;
    }
    
    public ArrayList getColumnMappingList(Connection conn){
    	ArrayList rslt = new ArrayList();
    	PreparedStatement pStmt = null;
    	ResultSet rs = null;
    	try {
        	String query = getQuerySql();
            Logger.log(this, query);
            pStmt = conn.prepareStatement(query);
            rs = pStmt.executeQuery();

            while (rs.next()) {
            	ExcelCellVo cell = (ExcelCellVo) getResultSetValues(rs);
                rslt.add(cell);
            }
        } catch (SQLException sqe) {
        	sqe.printStackTrace();
        	Logger.logExceptions(sqe);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(this, sqe);
            }
        }
        
        return rslt;
    }
}
